

use OnlineFloralDelivery
--use master
--drop database OnlineFloralDelivery
--create database OnlineFloralDelivery
go
create table Occasions(
	OccasionID int identity primary key,
	OccasionName nvarchar(200) not null,
	IsEnable bit default 1 not null
)

create table Bouquets(
	BouquetID int identity primary key,
	OccasionID int foreign key references Occasions(OccasionID) not null,
	[Name] nvarchar(200) not null,
	Description nvarchar(max),
	Price money not null,
	Quantity int not null,
	IsEnable bit default 1 not null
)

create table Messages(
	MessageID int identity primary key,
	OccasionID int foreign key references Occasions(OccasionID) not null,
	MessageContent nvarchar(max) not null,
	IsEnable bit default 1 not null
)

create table Billing(
	BillID int identity primary key,
	CustomerID uniqueidentifier foreign key references dbo.aspnet_Users(UserId) not null,
	BillDate datetime,
	TotalAmount money not null
)

create table ShippingDetails(
	ShippingID int identity primary key,
	BillID int foreign key references Billing(BillID) not null,
	CustomerID uniqueidentifier foreign key references dbo.aspnet_Users(UserId) not null,
	[Name] nvarchar(200) not null,
	Address nvarchar(max) not null,
	Phone nchar(20),
	MobilePhone nchar(20),
	Note nvarchar(max),
	[Message] nvarchar(max),
	DeliveryDate datetime not null,
	IsEnable bit default 1 not null
)

create table Orders(
	OrderID int identity primary key,
	ShippingID int foreign key references ShippingDetails(ShippingID) not null,
	CustomerID uniqueidentifier foreign key references dbo.aspnet_Users(UserId) not null,
	BouquetID int foreign key references Bouquets(BouquetID) not null,
	CurrentPrice money not null,
	Quantity int not null,
	OrderDate datetime default GetDate() not null,
)


create table News(
	NewsID int identity primary key,
	Title nvarchar(200) not null,
	[Content] nvarchar(max) not null,
	PostedDate datetime default GetDate() not null,
	IsEnable bit default 1 not null
)
go
declare @var uniqueidentifier
set @var = 'A972C577-DFB0-064E-1189-0154C99310DAAC12'
insert into Billing(CustomerID, TotalAmount) values(@var, 4444)
insert into ShippingDetails(BillID,CustomerID,[Name],Address,DeliveryDate)
values(1, 'user', 'adas', 'afdfdsfdsf', getdate() + 1)
select * from Orders
insert into Occasions(OccasionName) values('occa 1')
insert into Bouquets(OccasionID,[Name],Price,Quantity)
values(1, 'afdf', 656.5, 45)
insert into Orders(shippingID,CustomerID,BouquetID,CurrentPrice,Quantity)
values(1, 'user', 1, 344.4, 55)
insert into Messages(OccasionID,MessageContent)
values(1, 'message demo')
insert into News(Title,[Content])
values('new 1', 'content here')